SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;
	l_total_extracted_2 NUMBER;
	l_total_imported_2 NUMBER;
	l_total_extracted_3 NUMBER;
	l_total_imported_3 NUMBER;				
	l_func_id			VARCHAR2(100):='CAPPOREC_ATTACHMENT';
	l_code VARCHAR2(100):= UPPER('&1');

BEGIN
select sum(cnt) 
into l_total_extracted
from (
    select COUNT(1) cnt
    from   fnd_attached_documents fad,
    	   po_headers_all ph,
    	   hr_organization_information hoi
    where  fad.entity_name = 'PO_HEADERS'
    and    fad.pk1_value = ph.po_header_id
    and    ph.org_id = hoi.organization_id
    and    hoi.org_information_context = 'Operating Unit Information' 
    and    hoi.org_information3 = (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    union all
    select COUNT(1) cnt
    from   fnd_attached_documents fad,
    	   po_lines_all pl,
    	   hr_organization_information hoi
    where  fad.entity_name in ('PO_LINES', 'PO_IN_GA_LINES')
    and    fad.pk1_value = pl.po_line_id
    and    pl.org_id = hoi.organization_id
    and    hoi.org_information_context = 'Operating Unit Information' 
    and    hoi.org_information3= (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    union all
    select COUNT(1) cnt
    from   fnd_attached_documents fad,
    	   po_requisition_headers_all prh,
    	   hr_organization_information hoi
    where  fad.entity_name = 'REQ_HEADERS'
    and    fad.pk1_value = prh.requisition_header_id
    and    prh.org_id = hoi.organization_id
    and    hoi.org_information_context = 'Operating Unit Information' 
    and    hoi.org_information3 =  (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    union all
    select COUNT(1) cnt
    from   fnd_attached_documents fad,
    	   po_requisition_lines_all prl,
    	   hr_organization_information hoi
    where  fad.entity_name = 'REQ_LINES'
    and    fad.pk1_value = prl.requisition_line_id
    and    prl.org_id = hoi.organization_id
    and    hoi.org_information_context = 'Operating Unit Information' 
    and    hoi.org_information3 =  (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
);

	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_FND_ATTACHED_DOCUMENTS;

	select COUNT(1)
	INTO l_total_extracted_2
	from   FND_DOCUMENTS
	where  DOCUMENT_ID IN 	(
						select fad.DOCUMENT_ID
    					from   	fnd_attached_documents fad,
    	   						po_headers_all ph,
    	   						hr_organization_information hoi
    					where  	fad.entity_name = 'PO_HEADERS'
    					And    fad.pk1_value = ph.po_header_id
    						and    ph.org_id = hoi.organization_id
    						and    hoi.org_information_context = 'Operating Unit Information' 
    						and    hoi.org_information3 = (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    					union all
    					select fad.DOCUMENT_ID 
    					from   fnd_attached_documents fad,
    	   					po_lines_all pl,
    	   					hr_organization_information hoi
    					where  fad.entity_name in ('PO_LINES', 'PO_IN_GA_LINES')
    						and    fad.pk1_value = pl.po_line_id
    						and    pl.org_id = hoi.organization_id
    						and    hoi.org_information_context = 'Operating Unit Information' 
    						and    hoi.org_information3 =  (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    					union all
    					select fad.DOCUMENT_ID
    					from   fnd_attached_documents fad,
    	   					po_requisition_headers_all prh,
    	   					hr_organization_information hoi
    					where  fad.entity_name = 'REQ_HEADERS'
    						and    fad.pk1_value = prh.requisition_header_id
    						and    prh.org_id = hoi.organization_id
    						and    hoi.org_information_context = 'Operating Unit Information' 
    						and    hoi.org_information3= (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    					union all
    					select fad.DOCUMENT_ID 
    					from   fnd_attached_documents fad,
    	   					po_requisition_lines_all prl,
    	   					hr_organization_information hoi
    					where  fad.entity_name = 'REQ_LINES'
    						and    fad.pk1_value = prl.requisition_line_id
    						and    prl.org_id = hoi.organization_id
    						and    hoi.org_information_context = 'Operating Unit Information' 
    						and    hoi.org_information3= (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
					);

	SELECT COUNT(1) 
	INTO l_total_imported_2
	FROM CRST_FND_DOCUMENTS;

	select COUNT(1)
	INTO l_total_extracted_3
	from   FND_DOCUMENTS_TL
	where  DOCUMENT_ID IN 	(
						select fad.DOCUMENT_ID    					
    					from   fnd_attached_documents fad,
    	   					po_headers_all ph,
    	   					hr_organization_information hoi
    					where  fad.entity_name = 'PO_HEADERS'
    						and    fad.pk1_value = ph.po_header_id
    						and    ph.org_id = hoi.organization_id
    						and    hoi.org_information_context = 'Operating Unit Information' 
    						and    hoi.org_information3= (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    					union all
    					select fad.DOCUMENT_ID 
    					from   fnd_attached_documents fad,
    	   					po_lines_all pl,
    	   					hr_organization_information hoi
    					where  fad.entity_name in ('PO_LINES', 'PO_IN_GA_LINES')
    						and    fad.pk1_value = pl.po_line_id
    						and    pl.org_id = hoi.organization_id
    						and    hoi.org_information_context = 'Operating Unit Information' 
    						and    hoi.org_information3 = (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    					union all
    					select fad.DOCUMENT_ID 
    					from   fnd_attached_documents fad,
    	   					po_requisition_headers_all prh,
    	   					hr_organization_information hoi
    					where  fad.entity_name = 'REQ_HEADERS'
    						and    fad.pk1_value = prh.requisition_header_id
    						and    prh.org_id = hoi.organization_id
    						and    hoi.org_information_context = 'Operating Unit Information' 
    						and    hoi.org_information3 = (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
    					union all
    					select fad.DOCUMENT_ID 
    					from   fnd_attached_documents fad,
    	   					po_requisition_lines_all prl,
    	   					hr_organization_information hoi
    					where  fad.entity_name = 'REQ_LINES'
    						and    fad.pk1_value = prl.requisition_line_id
    						and    prl.org_id = hoi.organization_id
    						and    hoi.org_information_context = 'Operating Unit Information' 
    						and    hoi.org_information3= (select set_of_books_id 
								from   gl_sets_of_books 
								where  upper(short_name) = upper(l_code))
					);

	SELECT COUNT(1) 
	INTO l_total_imported_3
	FROM CRST_FND_DOCUMENTS_TL;
	
	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	dbms_output.put_line('Total Extracted_2: '||TO_CHAR(l_total_extracted_2)) ;
	dbms_output.put_line('Total Imported_2:  '||TO_CHAR(l_total_imported_2));
	dbms_output.put_line('Total Extracted_3: '||TO_CHAR(l_total_extracted_3)) ;
	dbms_output.put_line('Total Imported_3:  '||TO_CHAR(l_total_imported_3));
	
	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;	
	
	IF l_total_extracted_2 <> l_total_imported_2 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;
	
	IF l_total_extracted_3 <> l_total_imported_3 THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
